LIKE Operator

LIKE Operator#

In this lesson we’ll learn how to query the data using the LIKE operator in the WHERE clause.

Example Syntax#

SELECT col1, col2, … coln

FROM table

WHERE col3 LIKE "%some-string%"

Connect to the terminal below by clicking in the widget. Once connected, the command line prompt will show up. Enter or copy and paste the command ./DataJek/Lessons/9lesson.sh and wait for the MySQL prompt to start-up.

The LIKE operator works only with string data types and allows us to retrieve rows based on pattern matching on a particular column.

1. Say, you have forgotten the full name of a particular actor but remember that the name starts with the string “Jen”. You can check if there is an actor with a name that has “Jen” as the prefix by executing the following query in the terminal.

   SELECT * from Actors WHERE FirstName LIKE "Jen%";
   

Note that we use the string “Jen%” and not “Jen”. In fact, if you used the latter, no rows will be matched.

SELECT * from Actors WHERE FirstName LIKE "Jen";

The % symbol is a wildcard character that matches all strings. It can match zero or more characters. For instance, the following query returns one row and the wildcard character matches zero characters.

SELECT * from Actors where FirstName LIKE "Jennifer%";

As a final example, executing the following query will match all the rows in the table.

SELECT * from Actors where FirstName LIKE "%";

2. We can use the underscore character to match exactly one character. For instance, the expression LIKE "_enn%" will match the string "Jennifer”.

   SELECT * from Actors WHERE FirstName LIKE "_enn%";
   

The LIKE clause can also be used with the SHOW command. For example:

SHOW DATABASES LIKE "M%";

Note that the LIKE statement is case sensitive. It shows different results for patterns “%M” and “%m”. We can also use LIKE when listing tables as the following example demonstrates:

SHOW TABLES LIKE "A%";